-- @owner: wan005
-- @date: 2024/10/8
-- @testpoint: pipelined函数返回顺序数组类型，部分合理报错
-- @modify: modified by zhangbingliang at 2025.01,优化用例适配代码变更
--pre1:切换A库执行
@conn PrimaryDbAmode;

--step1:创建嵌套表类型;expect:成功
drop table if exists t_pipelined_0005;
create table t_pipelined_0005(c1 integer, c2 tinyint, c3 numeric(10, 4), c4 decimal(12),
c5 number(10,4), c6 float(10), c7 bit(1), c8 dec(10,3), c9 boolean, c10 char(10),
c11 nchar(10), c12 varchar(200), c13 varchar2(20), c14 text, c15 blob, c16 date, c17 time);
INSERT INTO t_pipelined_0005 (
    c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17
) VALUES (-12345,112,1234.5678,123456789012,-1234.5678,1234.56789,b'1',123.456,true,
    'char10','nchar10','varchar200 is a long string...','varchar20','This is a text field content.',
    NULL,'2023-10-23','12:34:56');
create or replace type array_type_0005_1 as varray(10) of integer;
create or replace type array_type_0005_2 as varray(10) of tinyint;
create or replace type array_type_0005_3 as varray(10) of float(10);
create or replace type array_type_0005_4 as varray(10) of decimal(12);
create or replace type array_type_0005_5 as varray(10) of char(10);
create or replace type array_type_0005_6 as varray(10) of date;
create or replace type array_type_0005_7 as varray(10) of time;

--step2:创建管道函数并查看返回值;expect:成功
create or replace function func_pipelined_002_1(count in number)
returns array_type_0005_1 pipelined
as $$
declare result integer;
begin
for i in 1 .. count loop
result = (select c1 from t_pipelined_0005 where c1 = -12345);
pipe row(result);
result := array[1, -2, 0, -0, 6, 7];
pipe row(result[i]);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_2(count in number)
returns array_type_0005_2 pipelined
as $$
declare result tinyint;
begin
for i in 1 .. count loop
result = (select c2 from t_pipelined_0005 where c2 = 112);
pipe row(result);
result := array[1, -2, 0, -0, 6, 7];
pipe row(result[i]);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_3(count in number)
returns array_type_0005_3 pipelined
as $$
declare result float(10);
begin
for i in 1 .. count loop
result := array[8.345, -2.345, 0.00, -0.00, 6, 7];
pipe row(result[i]);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_4(count in number)
returns array_type_0005_4 pipelined
as $$
declare result decimal(12);
begin
for i in 1 .. count loop
result = (select c4 from t_pipelined_0005 limit 1);
pipe row(result);
result := array[1, -2, 0, -0, 6, 7];
pipe row(result[i]);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_5(count in number)
returns array_type_0005_5 pipelined
as $$
declare result char(10);
begin
for i in 1 .. count loop
result = (select c10 from t_pipelined_0005 where c10 like '%char%');
pipe row(result);
result := array['sdf', 1, 'sdfw@#$%@', date'2022-01-01', 6, 7];
pipe row(result[i]);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_6(count in number)
returns array_type_0005_6 pipelined
as $$
declare result date;
begin
for i in 1 .. count loop
result = (select c16 from t_pipelined_0005 order by c16 limit 1);
pipe row(result);
result := array[20220101, '20220101', '2022-01-01', date'2022-01-01', current_date];
pipe row(result[i]);
end loop;
return;
end;
$$ language plpgsql;
/

create or replace function func_pipelined_002_7(count in number)
returns array_type_0005_7 pipelined
as $$
declare result time;
begin
for i in 1 .. count loop
result = (select c17 from t_pipelined_0005);
pipe row(result);
result := array['11:11:11', time'11:11:11', time'11:11:11.666', current_time, time'23:59:59'];
pipe row(result[i]);
end loop;
return;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:失败
select func_pipelined_002_1(5);
select * from func_pipelined_002_2(5);
select * from table(func_pipelined_002_3(5));
select func_pipelined_002_4(5);
select * from func_pipelined_002_5(5);
select * from table(func_pipelined_002_6(5));
select * from table(func_pipelined_002_7(5));

--step4:使用create table as创建表;expect:失败
drop table if exists t_pipelined_0005_8;
create table t_pipelined_0005_8 as select * from func_pipelined_002_1(1);
insert into t_pipelined_0005_8 (select * from table(func_pipelined_002_1(1)));
select * from t_pipelined_0005_8;

--step5:清理环境;expect:成功
drop table if exists t_pipelined_0005;
drop type if exists tb_type_0002_1 cascade;
drop type if exists tb_type_0002_2 cascade;
drop type if exists tb_type_0002_3 cascade;
drop type if exists tb_type_0002_4 cascade;
drop type if exists tb_type_0002_5 cascade;
drop type if exists tb_type_0002_6 cascade;
drop type if exists tb_type_0002_7 cascade;
drop function if exists func_pipelined_002_1;
drop function if exists func_pipelined_002_2;
drop function if exists func_pipelined_002_3;
drop function if exists func_pipelined_002_4;
drop function if exists func_pipelined_002_5;
drop function if exists func_pipelined_002_6;
drop function if exists func_pipelined_002_7;
drop table if exists t_pipelined_0005;
drop table if exists t_pipelined_0005_8;
